package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.thread.MainWorker;

public class DBMain {
	public static String dbname = DBCommon.dbname ;
	public static String dbuser = DBCommon.dbuser ;
	public static String dbpassword = DBCommon.dbpassword ;
	public static String dbur1 = DBCommon.dbur1;
	
	static Logger logger = Logger.getLogger(DBMain.class);  
	
	public static com.alibaba.fastjson.JSONArray getFilterList(String statusin) {
		com.alibaba.fastjson.JSONArray rets = new com.alibaba.fastjson.JSONArray();
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error("" + e) ;
					}
					try {
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "SELECT SettingId,username,secret,loginType,IP,port,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath,netType,type,date_format(time, '%Y-%m-%d %H:%i:%s'),frequency,renewnum,number,status,articleId,firstsort,secendsort,tags,params from publish_setting where 1=1 ";
						if(StringUtils.isNotBlank(statusin)){
							sqlstr += " and status='"+statusin+"' " ;
						}
						sqlstr += "order by SettingId desc " ;
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						ResultSet set = sql.executeQuery(sqlstr);
						// System.out.println(set.next());

						while (set.next()) {
							com.alibaba.fastjson.JSONObject cObj = new com.alibaba.fastjson.JSONObject();
							String SettingId = set.getInt(1)+"";
							String username = set.getString(2);
							String secret = set.getString(3);
							String loginType = set.getString(4);
							String IP = set.getString(5);
							int port = set.getInt(6);
							String title = set.getString(7);
							String content = set.getString(8);
							int isImediate = set.getInt(9);
							String publishTime = set.getString(10);
							String photos = set.getString(11);
							String fengmian = set.getString(12);
							String videopath = set.getString(13);
							String portalphotopath = set.getString(14);
							String netType = set.getString(15);
							String type = set.getString(16);
							String time = set.getString(17);
							String frequency = set.getString(18);
							int renewnum = set.getInt(19);
							int number = set.getInt(20);
							String status = set.getString(21);
							String articleId = set.getString(22);
							String firstsort = set.getString(23);
							String secendsort = set.getString(24);
							String tags = set.getString(25);
							String params = set.getString(26);
							
							cObj.put("firstsort", firstsort);
							cObj.put("secendsort", secendsort);
							cObj.put("tags", tags);
							cObj.put("params", params);

							cObj.put("SettingId", SettingId);
							cObj.put("username", username);
							cObj.put("secret", secret);
							cObj.put("loginType", loginType);
							cObj.put("IP", IP);
							cObj.put("port", port);
							cObj.put("title", title);
							cObj.put("content", content);
							cObj.put("isImediate", isImediate);
							cObj.put("publishTime", publishTime);
							cObj.put("photos", photos);
							cObj.put("fengmian", fengmian);
							cObj.put("videopath", videopath);
							cObj.put("portalphotopath", portalphotopath);
							cObj.put("netType", netType);
							cObj.put("type", type);
							cObj.put("time", time);
							cObj.put("frequency", frequency);
							cObj.put("renewnum", renewnum);
							cObj.put("number", number);
							cObj.put("status", status);
							cObj.put("articleId", articleId);
							
							rets.add(cObj);
						}
						set.close();
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static com.alibaba.fastjson.JSONArray getFilterListById(String settingid) {
		com.alibaba.fastjson.JSONArray rets = new com.alibaba.fastjson.JSONArray();
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error("" + e) ;
					}
					try {
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "SELECT SettingId,username,secret,loginType,IP,port,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath,netType,type,date_format(time, '%Y-%m-%d %H:%i:%s'),frequency,renewnum,number,status,articleId from publish_setting where 1=1 ";
						if(StringUtils.isNotBlank(settingid)){
							sqlstr += " and SettingId="+settingid+" " ;
						}
						sqlstr += "order by SettingId desc " ;
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						ResultSet set = sql.executeQuery(sqlstr);
						// System.out.println(set.next());

						while (set.next()) {
							com.alibaba.fastjson.JSONObject cObj = new com.alibaba.fastjson.JSONObject();
							String SettingId = set.getInt(1)+"";
							String username = set.getString(2);
							String secret = set.getString(3);
							String loginType = set.getString(4);
							String IP = set.getString(5);
							int port = set.getInt(6);
							String title = set.getString(7);
							String content = set.getString(8);
							int isImediate = set.getInt(9);
							String publishTime = set.getString(10);
							String photos = set.getString(11);
							String fengmian = set.getString(12);
							String videopath = set.getString(13);
							String portalphotopath = set.getString(14);
							String netType = set.getString(15);
							String type = set.getString(16);
							String time = set.getString(17);
							String frequency = set.getString(18);
							int renewnum = set.getInt(19);
							int number = set.getInt(20);
							String status = set.getString(21);
							String articleId = set.getString(22);
							
							String firstsort = set.getString(23);
							String secendsort = set.getString(24);
							String tags = set.getString(25);
							String params = set.getString(26);
							
							cObj.put("firstsort", firstsort);
							cObj.put("secendsort", secendsort);
							cObj.put("tags", tags);
							cObj.put("params", params);

							cObj.put("SettingId", SettingId);
							cObj.put("username", username);
							cObj.put("secret", secret);
							cObj.put("loginType", loginType);
							cObj.put("IP", IP);
							cObj.put("port", port);
							cObj.put("title", title);
							cObj.put("content", content);
							cObj.put("isImediate", isImediate);
							cObj.put("publishTime", publishTime);
							cObj.put("photos", photos);
							cObj.put("fengmian", fengmian);
							cObj.put("videopath", videopath);
							cObj.put("portalphotopath", portalphotopath);
							cObj.put("netType", netType);
							cObj.put("type", type);
							cObj.put("time", time);
							cObj.put("frequency", frequency);
							cObj.put("renewnum", renewnum);
							cObj.put("number", number);
							cObj.put("status", status);
							cObj.put("articleId", articleId);
							
							rets.add(cObj);
						}
						set.close();
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static int getCyclte(String cyclename) {
		int rets = -1;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "SELECT cycleValue from publish_cycle where 1=1  and cycleName='"+cyclename+"' ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						ResultSet set = sql.executeQuery(sqlstr);
						// System.out.println(set.next());

						while (set.next()) {
							rets = set.getInt(1);
						}
						set.close();
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static boolean updateSettingStatus(String settingids, String status) {
		boolean rets = false;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
						String nowDate = sdf.format(new Date()) ;
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "update publish_setting set status='"+status+"',updatetime='"+nowDate+"'  where SettingId in ("+settingids+") ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						sql.execute(sqlstr);
						// System.out.println(set.next());
						rets = true;
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static boolean updateSettingArticleId(String settingids, String articleid) {
		boolean rets = false;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
						String nowDate = sdf.format(new Date()) ;
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "update publish_setting set articleId='"+articleid+"',updatetime='"+nowDate+"'  where SettingId='"+settingids+"' ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						sql.execute(sqlstr);
						// System.out.println(set.next());
						rets = true;
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static boolean updateSettingcodeId(String settingids, String codeId) {
		boolean rets = false;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
						String nowDate = sdf.format(new Date()) ;
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "update publish_setting set codeId="+codeId+",updatetime='"+nowDate+"'  where SettingId="+settingids+" ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						sql.execute(sqlstr);
						// System.out.println(set.next());
						rets = true;
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static String insertResult(String username , String loginType, String netType , String result, String type, String SettingId, String  status ) {
		String rets = null ;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
						
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						String sqlstr = "insert into publish_result(username , loginType, netType , result, type , SettingId, status ) values('"+username+"','"+loginType+"','"+netType+"','"+result+"','"+type+"',"+SettingId+",'"+status+"') ";
						System.out.println(sqlstr);
						sql.executeUpdate(sqlstr, Statement.RETURN_GENERATED_KEYS);
						// System.out.println(set.next());
						ResultSet set = sql.getGeneratedKeys(); 

						if (set.next()) {
							int keyValue = set.getInt(1); 
							rets = keyValue+"" ;
						}
						set.close();
						conn.close();

						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						rets = null;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	
	public static String insertCode(String netType, String publishtype, String codetype, String Status, String Settingid, String photos) {
		String rets = null ;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
						
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						String sqlstr = "insert into publish_code(netType, publishtype, photo, codetype, Status, Settingid) values('"+netType+"','"+publishtype+"','"+photos+"','"+codetype+"','"+Status+"',"+Settingid+") ";
						System.out.println(sqlstr);
						sql.executeUpdate(sqlstr, Statement.RETURN_GENERATED_KEYS);
						// System.out.println(set.next());
						ResultSet set = sql.getGeneratedKeys(); 

						if (set.next()) {
							int keyValue = set.getInt(1); 
							rets = keyValue+"" ;
						}
						set.close();
						conn.close();

						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						rets = null;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static String updateCodeStatus(String ids, String status) {
		String rets = null ;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
						
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						String sqlstr = "update publish_code set Status='"+status+"' where id="+ids+" ";
						System.out.println(sqlstr);
						sql.execute(sqlstr);
						// System.out.println(set.next());
						
						

						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						rets = null;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	
	public static boolean updateCnts(String Settingid, String pinglun, String zhuanfa, String shoucang, String dianji) {
		boolean rets = false;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
						String nowDate = sdf.format(new Date()) ;
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "update publish_setting set pinglun="+pinglun+",zhuanfa="+zhuanfa+",shoucang="+shoucang+",dianji="+dianji+"  where SettingId ='"+Settingid+"' ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						sql.execute(sqlstr);
						// System.out.println(set.next());
						rets = true;
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}

	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
//		DBMain.updateSettingStatus("'1'", "5") ;
		try{
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
			String strs = "2016-12-28 20:11:20" ;
			sdf.parse(strs) ;
		}catch(Exception e){
			e.printStackTrace() ;
		}
		
//		
//		com.alibaba.fastjson.JSONArray list = DBMain.getFilterList("1");
//		if(list.size() > 0){
//			
//		}
	}

}
